﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Collections;
using System.Data.Common;

namespace InteractiveMapApp
{
    /// <summary>
    /// SQLiteDBHelper Class
    /// Made for managing the local DB, local DB should be much faster
    /// to perform queries on (sqllite less overheads, also we wont have 
    /// to deal with the http transfer overheads & the stress already on the
    /// windows server/sql db that is dealing out content to the web sites&
    /// other terminals)
    /// </summary>
    class SQLiteDBHelper
    {
        String dbConnection;
        SQLiteConnection mainCnn;

        /// <summary>
        /// Default Constructor Constructor for specifying the DB file.
        /// </summary>
        /// <param name="inputFile">The DB File</param>
        public SQLiteDBHelper(String inputFile)
        {
            dbConnection = String.Format("Data Source={0}", inputFile);
        }

        public void CreateTheTables(string create_str)
        {
            try
            {
                if (mainCnn == null)
                {
                    mainCnn = new SQLiteConnection(dbConnection);
                    mainCnn.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand(create_str, mainCnn);
                cmd.ExecuteNonQuery();

                //cnn.Close();
            }
            catch(Exception failure) 
            {
                Console.WriteLine("Error : " + failure.Message);
            }
        }

        /// <summary>
        /// Send sql query & get datatable
        /// </summary>
        /// <param name="sql">The SQL to run</param>
        /// <returns>A DataTable containing the result set.</returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                //SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                //cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(mainCnn);
                mycommand.CommandText = sql;
                SQLiteDataReader reader = mycommand.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                //cnn.Close();
            }
            catch (Exception e)
            {
                //throw new Exception(e.Message);
                Console.WriteLine("Error : " + e.Message);
            }
            return dt;
        }

        /// <summary>
        /// Interact with the database for purposes other than a query
        /// </summary>
        /// <param name="sql">The SQL to be run.</param>
        /// <returns>An Integer containing the number of rows updated.</returns>
        public int ExecuteNonQuery(string sql)
        {
            //SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            //cnn.Open();
            SQLiteCommand mycommand = new SQLiteCommand(mainCnn);
            mycommand.CommandText = sql;
            int rowsUpdated = mycommand.ExecuteNonQuery();
            //cnn.Close();
            return rowsUpdated;
        }


        /// <summary>
        /// Easily update rows in the DB.
        /// </summary>
        /// <param name="tableName">The table to update.</param>
        /// <param name="data">A dictionary containing Column names and their new values.</param>
        /// <param name="where">The where clause for the update statement.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Update(String tableName, Dictionary<String, String> data, String where)
        {
            String vals = "";
            Boolean returnCode = true;
            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in data)
                {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
                }
                vals = vals.Substring(0, vals.Length - 1);
            }
            try
            {
                this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
            }
            catch
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        /// Allows the programmer to easily delete rows from the DB.
        /// </summary>
        /// <param name="tableName">The table from which to delete.</param>
        /// <param name="where">The where clause for the delete. If where = null, delete ALL records!</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Delete(String tableName, String where)
        {
            Boolean returnCode = true;
            try
            {
                if (where == null)
                {
                    
                    this.ExecuteNonQuery("delete from " + tableName.ToString() + ";");
                }
                else
                {
                    this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        /// Allows the programmer to easily insert into the DB
        /// </summary>
        /// <param name="tableName">The table into which we insert the data.</param>
        /// <param name="data">A dictionary containing the column names and data for the insert.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Insert(String tableName, Dictionary<String, String> data)
        {
            String columns = "";
            String values = "";
            Boolean returnCode = true;
            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value);
            }
            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);
            try
            {
                this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        /// Takes arraylist of objects, iterates through them
        /// and within a single transaction (good performance!)
        /// adds all the objects in to the required table on the local DB
        /// </summary>
        /// <param name="stories">arraylist of TimeSlot objects</param>
        public void MassInsertTimeSlots(ArrayList timeslots)
        {
            //SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            //cnn.Open();
            using (DbTransaction dbTrans = mainCnn.BeginTransaction())
            {

                using (DbCommand cmd = mainCnn.CreateCommand())
                {
                    //unselectImgURL,normalImgURL,timeslotID,isItFilteredOut
                    cmd.CommandText = "INSERT INTO TIMESLOT VALUES(?,?,?,?,?)";

                    DbParameter Field1 = cmd.CreateParameter();
                    DbParameter Field2 = cmd.CreateParameter();
                    DbParameter Field3 = cmd.CreateParameter();
                    DbParameter Field4 = cmd.CreateParameter();
                    DbParameter Field5 = cmd.CreateParameter();
                    cmd.Parameters.Add(Field1);
                    cmd.Parameters.Add(Field2);
                    cmd.Parameters.Add(Field3);
                    cmd.Parameters.Add(Field4);
                    cmd.Parameters.Add(Field5);
                    foreach (TimeSlot i in timeslots)
                    {
                        Field1.Value = i.UnselectedImgURL;
                        Field2.Value = i.SelectedImgURL;
                        Field3.Value = i.TimeSlotID;
                        Field4.Value = "false";
                        Field5.Value = i.Read_timeslotURL;
                        cmd.ExecuteNonQuery();
                    }
                }
                dbTrans.Commit();
            }
        }

        /// <summary>
        /// Takes arraylist of objects, iterates through them
        /// and within a single transaction (good performance!)
        /// adds all the objects in to the required table on the local DB
        /// </summary>
        /// <param name="stories">arraylist of Category objects</param>
        public void MassInsertCategories(ArrayList categories)
        {
            //SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            //cnn.Open();
            using (DbTransaction dbTrans = mainCnn.BeginTransaction())
            {

                using (DbCommand cmd = mainCnn.CreateCommand())
                {
                    //img_UNselectedInputbox,img_selectedInputBox,
                    //unselectedFilterImage,adminStory,markerNormalImgUrl,
                    //normalImgURL,categoryDesc,categoryID,isItFilteredOut
                    cmd.CommandText = "INSERT INTO CATEGORY VALUES(?,?,?,?,?,?,?,?,?)";

                    DbParameter Field1 = cmd.CreateParameter();
                    DbParameter Field2 = cmd.CreateParameter();
                    DbParameter Field3 = cmd.CreateParameter();
                    DbParameter Field4 = cmd.CreateParameter();
                    DbParameter Field5 = cmd.CreateParameter();
                    DbParameter Field6 = cmd.CreateParameter();
                    DbParameter Field7 = cmd.CreateParameter();
                    DbParameter Field8 = cmd.CreateParameter();
                    DbParameter Field9 = cmd.CreateParameter();
                    cmd.Parameters.Add(Field1);
                    cmd.Parameters.Add(Field2);
                    cmd.Parameters.Add(Field3);
                    cmd.Parameters.Add(Field4);
                    cmd.Parameters.Add(Field5);
                    cmd.Parameters.Add(Field6);
                    cmd.Parameters.Add(Field7);
                    cmd.Parameters.Add(Field8);
                    cmd.Parameters.Add(Field9);
                    foreach (Category i in categories)
                    {
                        Field1.Value = i.Input_unselectedImgURL;
                        Field2.Value = i.Input_selectedImgURL;
                        Field3.Value = i.Filter_unselectedImgURL;
                        string adminStory = "0";
                        if (i.AdminStory) { adminStory = "1"; }
                        Field4.Value = adminStory;
                        Field5.Value = i.MarkerImageURL;
                        Field6.Value = i.Filter_selectedImgURL;
                        Field7.Value = i.CategoryDesc;
                        Field8.Value = i.CategoryID.ToString();
                        Field9.Value = "false";
                        cmd.ExecuteNonQuery();
                    }
                }
                dbTrans.Commit();
            }
        }

        public void MassUpdateCategoriesFilterStatus(ArrayList categories)
        {
            try
            {
                //SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                //cnn.Open();
                using (DbTransaction dbTrans = mainCnn.BeginTransaction())
                {
                    using (DbCommand cmd = mainCnn.CreateCommand())
                    {
                        cmd.CommandText = "UPDATE CATEGORY SET isItFilteredOut=? WHERE categoryID=?";

                        DbParameter Field1 = cmd.CreateParameter();
                        DbParameter Field2 = cmd.CreateParameter();
                        cmd.Parameters.Add(Field1);
                        cmd.Parameters.Add(Field2);
                        string isItFilteredOut = "false";
                        foreach (Category i in categories)
                        {
                            isItFilteredOut = "false";
                            if (!i.ShowFilter)
                            {
                                isItFilteredOut = "true";
                            }
                            Field1.Value = isItFilteredOut;
                            Field2.Value = i.CategoryID.ToString();
                            cmd.ExecuteNonQuery();
                        }
                    }
                    dbTrans.Commit();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error : Is the local DB file open? - " + e.Message);
            }
        }

        public void MassUpdateTimeSlotsFilterStatus(ArrayList timeslots)
        {
            //SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            //cnn.Open();
            using (DbTransaction dbTrans = mainCnn.BeginTransaction())
            {
                using (DbCommand cmd = mainCnn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE TIMESLOT SET isItFilteredOut=? WHERE timeslotID=?";

                    DbParameter Field1 = cmd.CreateParameter();
                    DbParameter Field2 = cmd.CreateParameter();
                    cmd.Parameters.Add(Field1);
                    cmd.Parameters.Add(Field2);
                    string isItFilteredOut = "false";
                    foreach (TimeSlot i in timeslots)
                    {
                        isItFilteredOut = "false";
                        if (!i.ShowFilter)
                        {
                            isItFilteredOut = "true";
                        }
                        Field1.Value = isItFilteredOut;
                        Field2.Value = i.TimeSlotID.ToString();
                        cmd.ExecuteNonQuery();
                    }
                }
                dbTrans.Commit();
            }
        }

        /// <summary>
        /// Takes arraylist of objects, iterates through them
        /// and within a single transaction (good performance!)
        /// adds all the objects in to the required table on the local DB
        /// </summary>
        /// <param name="stories">arraylist of Story objects</param>
        public void MassInsertStories(ArrayList stories)
        {
            //SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            //cnn.Open();
            using (DbTransaction dbTrans = mainCnn.BeginTransaction())
            {

                using (DbCommand cmd = mainCnn.CreateCommand())
                {
                    //storyID, categoryID, timeslotID, latitude, longitude, content, museumImageUrl, title

                    cmd.CommandText = "INSERT OR REPLACE INTO STORY VALUES(?,?,?,?,?,?,?,?)";

                    DbParameter Field1 = cmd.CreateParameter();
                    DbParameter Field2 = cmd.CreateParameter();
                    DbParameter Field3 = cmd.CreateParameter();
                    DbParameter Field4 = cmd.CreateParameter();
                    DbParameter Field5 = cmd.CreateParameter();
                    DbParameter Field6 = cmd.CreateParameter();
                    DbParameter Field7 = cmd.CreateParameter();
                    DbParameter Field8 = cmd.CreateParameter();
                    cmd.Parameters.Add(Field1);
                    cmd.Parameters.Add(Field2);
                    cmd.Parameters.Add(Field3);
                    cmd.Parameters.Add(Field4);
                    cmd.Parameters.Add(Field5);
                    cmd.Parameters.Add(Field6);
                    cmd.Parameters.Add(Field7);
                    cmd.Parameters.Add(Field8);

                    foreach(Story i in stories)
                    {
                        Field1.Value = i.StoryID.ToString();
                        Field2.Value = i.CategoryID.ToString();
                        Field3.Value = i.TimeSlotID.ToString();
                        Field4.Value = i.StoryLatLng.Lat.ToString();
                        Field5.Value = i.StoryLatLng.Lng.ToString();
                        Field6.Value = i.StoryContent;
                        Field7.Value = i.museumImgURL;
                        Field8.Value = i.Title;
                        cmd.ExecuteNonQuery();
                    }

                }
                dbTrans.Commit();
            }
        }

        /// <summary>
        /// Delete all data from the DB
        /// </summary>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearDB()
        {
            DataTable tables;
            try
            {
                tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
                foreach (DataRow table in tables.Rows)
                {
                    this.ClearTable(table["NAME"].ToString());
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Easily clear all data from a specific table
        /// </summary>
        /// <param name="table">The name of the table to clear.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearTable(String table)
        {
            try
            {
                this.ExecuteNonQuery(String.Format("delete from {0};", table));
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}
